﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Column = MvvmOrm.Models.Column.Row;
using DatabaseItemParameter = MvvmOrm.Models.DatabaseItemParameter.Row;
using DatabaseModel = MvvmOrm.Models.DatabaseModel.Row;

// new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");

namespace MvvmOrm.DbmsModules
{
    sealed class MssqlModule : DbmsModuleBase, IDbmsModule
    {
        #region singleton

        private static readonly MssqlModule instance = new MssqlModule();

        private MssqlModule() { }

        public static MssqlModule Instance
        {
            get
            {
                return instance;
            }
        }

        #endregion

        #region private

        SqlConnection _conn;

        static Column MapColumnInfo(DataRow row, bool is_view)
        {
            Column column = new Column();
            column.Name = (string)row["ColumnName"];
            column.MaxLength = (int)row["ColumnSize"];
            column.DbTypeEnum = GetDbType((SqlDbType)row["ProviderType"]);
            column.IsReadOnly = (bool)row["IsReadOnly"];
            column.Nullable = (bool)row["AllowDBNull"];
            if (!is_view) {
                column.IsPrimaryKey = (bool)row["IsKey"];
                column.IsAutoIncrement = (bool)row["IsAutoIncrement"];
            }
            return column;
        }

        static DbType GetDbType(SqlDbType sqlDbType)
        {
            return new SqlParameter("foo", sqlDbType).DbType;
        }

        static SqlDbType GetSqlDbType(DbType dbType)
        {
            var foo = new SqlParameter();
            foo.DbType = dbType;
            return foo.SqlDbType;
        }

        static SqlDbType GetSqlDbTypeFromString(string type)
        {
            switch (type.ToLower()) {
            case "bigint":
                return SqlDbType.BigInt;
            case "binary":
                return SqlDbType.Binary;
            case "bit":
                return SqlDbType.Bit;
            case "char":
                return SqlDbType.Char;
            case "datetime":
                return SqlDbType.DateTime;
            case "decimal":
                return SqlDbType.Decimal;
            case "float":
                return SqlDbType.Float;
            case "image":
                return SqlDbType.Image;
            case "integer":
            case "int":
                return SqlDbType.Int;
            case "money":
                return SqlDbType.Money;
            case "nchar":
                return SqlDbType.NChar;
            case "ntext":
                return SqlDbType.NText;
            case "nvarchar":
                return SqlDbType.NVarChar;
            case "real":
                return SqlDbType.Real;
            case "smalldatetime":
                return SqlDbType.SmallDateTime;
            case "smallint":
                return SqlDbType.SmallInt;
            case "smallmoney":
                return SqlDbType.SmallMoney;
            case "text":
                return SqlDbType.Text;
            case "timestamp":
                return SqlDbType.Timestamp;
            case "tinyint":
                return SqlDbType.TinyInt;
            case "uniqueidentifier":
                return SqlDbType.UniqueIdentifier;
            case "varbinary":
                return SqlDbType.VarBinary;
            case "varchar":
                return SqlDbType.VarChar;
            case "variant":
            case "sql_variant": 
                return SqlDbType.Variant;
            case "hierarchyid":
                return SqlDbType.NVarChar;
            }
            throw new Exception("unable to get SqlDbType for string: " + type);
        }

        static DatabaseItemParameter MapParameterInfo(SqlDataReader dr)
        {
            DatabaseItemParameter parameter = new DatabaseItemParameter();
            parameter.Name = dr["PARAMETER_NAME"] as string;
            parameter.DbTypeEnum = GetDbType(GetSqlDbTypeFromString(dr["DATA_TYPE"] as string));
            switch (dr["PARAMETER_MODE"] as string) {
            case "OUT":
                parameter.ParameterDirectionEnum = ParameterDirection.Output;
                break;
            case "IN":
                parameter.ParameterDirectionEnum = ParameterDirection.Input;
                break;
            case "INOUT":
                parameter.ParameterDirectionEnum = ParameterDirection.InputOutput;
                break;
            default:
                parameter.ParameterDirectionEnum = ParameterDirection.ReturnValue;
                break;
            }
            // TODO: scale, precision, size
            //parameter.Scale          = (byte)dr["NUMERIC_SCALE"];
            //parameter.Precision      = (byte)dr["DATA_TYPE_PREC"];
            //parameter.Size           = (short)dr["DATA_TYPE_LENGTH"];
            return parameter;
        }

        #endregion

        #region IDbmsModule

        public IEnumerable<string> AdditionalReferencedAssemblies
        {
            get { return new string[] { }; }
        }

        #endregion

        #region DbmsModuleBase

        protected override string GetDbTypeStringed(Column c)
        {
            return GetSqlDbType(c.DbTypeEnum).ToString();
        }

        protected override string GetDbTypeStringed(DatabaseItemParameter p)
        {
            return GetSqlDbType(p.DbTypeEnum).ToString();
        }

        public override void Connect(DatabaseModel model)
        {
            if (model.Server == "")
                throw new Exception("There must be a server specified");

            if (model.Database == "")
                throw new Exception("There must be a database specified");

            if (!(model.SqlServerUseIntegratedSecurity ?? false))
                if (!string.IsNullOrWhiteSpace(model.Username))
                    throw new Exception("There must be a username specified");

            if (model.SqlServerUseIntegratedSecurity ?? false)
                _conn = new SqlConnection("Persist Security Info=False;"
                    + "Server=" + model.Server + ";"
                    + "Initial Catalog=" + model.Database + ";"
                    + "Integrated Security=SSPI;");
            else
                _conn = new SqlConnection("Persist Security Info=False;"
                    + "Server=" + model.Server + ";"
                    + "Initial Catalog=" + model.Database + ";"
                    + "User ID=" + model.Username + ";"
                    + "Password=" + model.Password ?? string.Empty + ";");

            _conn.Open();
        }

        public override void AnalyseTables(DatabaseModel _model)
        {
            string sql = @"SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME <> 'sysdiagrams'
                ORDER BY TABLE_NAME";

            using (var command = new SqlCommand(sql, _conn))
            using (var dr = command.ExecuteReader()) {
                while (dr.Read()) {
                    string schema = dr.GetString(0);
                    //if ("dbo".Equals(schema, StringComparison.InvariantCultureIgnoreCase))
                    //    schema = null;
                    _model.AddTable(schema, dr.GetString(1));
                }
                dr.Close();
            }

            foreach (var table in _model.Tables)
                using (var command = new SqlCommand("SELECT * FROM " + Escape(table.Schema, table.Name), _conn))
                using (var dr = command.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly)) {
                    foreach (DataRow row in dr.GetSchemaTable().Rows)
                        table.Child_Columns.Add(MapColumnInfo(row, false));
                    dr.Close();
                }
        }

        public override void AnalyseViews(DatabaseModel _model)
        {
            string sql = @"SELECT TABLE_SCHEMA, TABLE_NAME
                FROM INFORMATION_SCHEMA.VIEWS
                ORDER BY TABLE_NAME";

            using (var command = new SqlCommand(sql, _conn))
            using (var dr = command.ExecuteReader()) {
                while (dr.Read()) {
                    string schema = dr.GetString(0);
                    //if ("dbo".Equals(schema, StringComparison.InvariantCultureIgnoreCase))
                    //    schema = null;
                    _model.AddView(schema, dr.GetString(1), true);
                }
                dr.Close();
            }

            foreach (var view in _model.Views)
                using (var command = new SqlCommand("SELECT * FROM " + Escape(view.Schema, view.Name), _conn))
                using (var dr = command.ExecuteReader(CommandBehavior.SchemaOnly)) {
                    foreach (DataRow row in dr.GetSchemaTable().Rows)
                        view.Child_Columns.Add(MapColumnInfo(row, true));
                    dr.Close();
                }
        }

        public override void AnalyseProcedures(DatabaseModel model)
        {
            string sql = @"SELECT ROUTINE_SCHEMA, ROUTINE_NAME
                    FROM INFORMATION_SCHEMA.ROUTINES
                    WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_NAME NOT LIKE 'sp_%'";

            using (var command = new SqlCommand(sql, _conn))
            using (var dr = command.ExecuteReader()) {
                while (dr.Read()) {
                    string schema = dr.GetString(0);
                    //if ("dbo".Equals(schema, StringComparison.InvariantCultureIgnoreCase))
                    //    schema = null;
                    model.AddProcedure(schema, dr.GetString(1));
                }
                dr.Close();
            }

            sql = @"SELECT * FROM information_schema.parameters 
                    WHERE specific_schema=@schema AND specific_name=@name
                    ORDER BY ordinal_position";
            foreach (var proc in model.Procedures) {

                // analyse parameters
                using (var command = new SqlCommand(sql, _conn)) {
                    command.Parameters.Add(new SqlParameter("@schema", proc.Schema));
                    command.Parameters.Add(new SqlParameter("@name", proc.Name));
                    using (var dr = command.ExecuteReader()) {
                        while (dr.Read())
                            proc.Child_DatabaseItemParameters.Add(MapParameterInfo(dr));
                        dr.Close();
                    }
                }

                // analyse columns
                using (var command = new SqlCommand(Escape(proc.Schema, proc.Name), _conn)) {
                    command.CommandType = CommandType.StoredProcedure;
                    foreach (var p in proc.Child_DatabaseItemParameters) {
                        var parameter = new SqlParameter(p.Name, GetSqlDbType(p.DbTypeEnum));
                        parameter.Direction = p.ParameterDirectionEnum;
                        command.Parameters.Add(parameter);
                    }
                    using (var dr = command.ExecuteReader(CommandBehavior.SchemaOnly)) {
                        var dt = dr.GetSchemaTable();
                        if (dt != null)
                            foreach (DataRow row in dt.Rows)
                                proc.Child_Columns.Add(MapColumnInfo(row, true));
                        dr.Close();
                    }
                }
            }
        }

        public override void AnalyseForeignKeys(DatabaseModel _model)
        {
            string sql = @"
SELECT DISTINCT
    FK.TABLE_SCHEMA AS FK_Schema,
    FK.TABLE_NAME AS FK_Table, 
    CU.COLUMN_NAME AS FK_Column, 
    PK.TABLE_SCHEMA AS PK_Schema,
    PK.TABLE_NAME AS PK_Table, 
    PT.COLUMN_NAME AS PK_Column
FROM 
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
    INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 
        ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME 
    INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 
        ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME 
    INNER JOIN 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU 
        ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
    INNER JOIN 
    ( 
        SELECT 
            i1.TABLE_NAME, i2.COLUMN_NAME 
        FROM 
            INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 
            INNER JOIN 
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 
            ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME 
            WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' 
    ) PT 
    ON PT.TABLE_NAME = PK.TABLE_NAME";

            using (var command = new SqlCommand(sql, _conn))
            using (var dr = command.ExecuteReader()) {
                while (dr.Read()) {

                    var constrained_table = _model.Tables.First(
                        t => t.Name == dr["FK_Table"] as string && t.Schema == dr["FK_Schema"] as string);
                    var constrained_column = constrained_table.Child_Columns.First(c => c.Name.ToLowerInvariant() == (dr["FK_Column"] as string).ToLowerInvariant());

                    var referenced_table = _model.Tables.First(
                        t => t.Name == dr["PK_Table"] as string && t.Schema == dr["PK_Schema"] as string);
                    var referenced_column = referenced_table.Child_Columns.First(c => c.Name.ToLowerInvariant() == (dr["PK_Column"] as string).ToLowerInvariant());

                    constrained_column.ReferencedColumnId = referenced_column.Id;
                }
                dr.Close();
            }
        }

        public override void Disconnect()
        {
            _conn.Close();
        }

        protected override string GetSpecificUsings()
        {
            return @"
using System.Data.SqlClient;
using DbType = System.Data.SqlDbType;";
        }

        protected override void GetDbConnectionSpecifics(StringBuilder s)
        {
            s.AppendLine(@"
        /// <summary>Gets or sets the Sql Server instance</summary>
        public string Server { get; set; }

        /// <summary>Gets or sets the database name</summary>
        public string Database { get; set; }

        /// <summary>Gets or sets the use Windows Authentication</summary>
        public bool WindowsAuthentication { get; set; }

        /// <summary>Gets or sets the username/summary>
        public string Username { get; set; }

        /// <summary>Sets the password</summary>
        public string Password { private get; set; }

        /// <summary>Inititalizes a new instance of the DbConnection class</summary>
        public DbConnection()
        {
            Server = """";
            Database = """";
            Username = """";
            Password = """";
            WindowsAuthentication = false;
            Initalize();
        }

        /// <summary>Inititalizes a new instance of the DbConnection class 
        /// using Sql Server Authentication</summary>
        /// <param name=""server"">The Sql Server instance</param>
        /// <param name=""database"">The database</param>
        /// <param name=""username"">The username</param>
        /// <param name=""password"">The password</param>
        public DbConnection(string server, string database, string username, string password)
        {
            Server = server;
            Database = database;
            Username = username;
            Password = password;
            WindowsAuthentication = false;
            Initalize();
        }

        /// <summary>Inititalizes a new instance of the DbConnection class 
        /// using Windows Authentication</summary>
        /// <param name=""server"">The Sql Server instance</param>
        /// <param name=""database"">The database</param>
        public DbConnection(string server, string database)
        {
            Server = server;
            Database = database;
            Username = """";
            Password = """";
            WindowsAuthentication = true;
            Initalize();
        }

        /// <summary>Opens the connection to the database</summary>
        public void Open()
        {
            if (WindowsAuthentication)
                _connection.ConnectionString = ""Persist Security Info=False;""
                            + ""Server="" + Server + "";""
                            + ""Initial Catalog="" + Database + "";""
                            + ""Integrated Security=SSPI;"";
            else
                _connection.ConnectionString = ""Persist Security Info=False;""
                            + ""Server="" + Server + "";""
                            + ""Initial Catalog="" + Database + "";""
                            + ""User ID="" + Username  + "";""
                            + ""Password="" + Password + "";"";
            _connection.Open();
        }

        /// <summary>Clone the DbConnection</summary>
        /// <returns>The cloned DbConnection</returns>
        public DbConnection Clone()
        {
            DbConnection conn = new DbConnection();
            conn.Server = Server;
            conn.Database = Database;
            conn.Username = Username;
            conn.Password = Password;
            conn.WindowsAuthentication = WindowsAuthentication;
            conn.AutoConnect = _autoConnect;
            conn.CommandTimeout = _commandTimeout;
            return conn;
        }
");
        }

        protected override string GetEscapeStart()
        {
            return "[";
        }

        protected override string GetEscapeEnd()
        {
            return "]";
        }

        protected override string GetDbTypeType()
        {
            return "System.Data.SqlDbType";
        }

        protected override string GetCommandType()
        {
            return "SqlCommand";
        }

        protected override string GetDataReaderType()
        {
            return "SqlDataReader";
        }

        protected override string GetConnectionType()
        {
            return "SqlConnection";
        }

        protected override string GetParameterType()
        {
            return "SqlParameter";
        }

        protected override string GetTransactionType()
        {
            return "SqlTransaction";
        }

        protected override string GetAutoIncrementSqlFunction()
        {
            return "CAST(SCOPE_IDENTITY() AS INT)";
        }

        #endregion
    }
}